package com.yupi.sqlfather.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yupi.sqlfather.common.ErrorCode;
import com.yupi.sqlfather.exception.BusinessException;
import com.yupi.sqlfather.mapper.DataSourceMapper;
import com.yupi.sqlfather.model.entity.DataSource;
import com.yupi.sqlfather.service.DataSourceService;
import com.yupi.sqlfather.utils.EncryptionUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据源服务实现
 */
@Service
@Slf4j
public class DataSourceServiceImpl extends ServiceImpl<DataSourceMapper, DataSource> implements DataSourceService {

    @Override
    public boolean testConnection(DataSource dataSource) {
        if (dataSource == null) {
            throw new BusinessException(ErrorCode.PARAMS_ERROR);
        }
        try {
            // 校验数据源信息
            validDataSource(dataSource, false);
            // 测试连接
            return getConnection(dataSource) != null;
        } catch (Exception e) {
            log.error("数据源连接测试失败", e);
            throw new BusinessException(ErrorCode.OPERATION_ERROR, "数据源连接测试失败: " + e.getMessage());
        }
    }

    @Override
    public void validDataSource(DataSource dataSource, boolean add) {
        if (dataSource == null) {
            throw new BusinessException(ErrorCode.PARAMS_ERROR);
        }
        String name = dataSource.getName();
        String url = dataSource.getUrl();
        String username = dataSource.getUsername();
        String password = dataSource.getPassword();
        String type = dataSource.getType();

        // 创建时，所有参数必须非空
        if (add && StringUtils.isAnyBlank(name, url, username, type)) {
            throw new BusinessException(ErrorCode.PARAMS_ERROR, "参数不能为空");
        }

        if (StringUtils.isNotBlank(name) && name.length() > 50) {
            throw new BusinessException(ErrorCode.PARAMS_ERROR, "名称过长");
        }

        if (StringUtils.isNotBlank(type)) {
            // 校验数据库类型
            if (!isSupportedDatabaseType(type)) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "不支持的数据库类型");
            }
        }

        if (StringUtils.isNotBlank(url)) {
            // 校验URL格式
            if (!isValidJdbcUrl(url, type)) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "数据库连接URL格式错误");
            }
        }
    }

    @Override
    public Object executeSql(Long dataSourceId, String sql) {
        if (dataSourceId == null || StringUtils.isBlank(sql)) {
            throw new BusinessException(ErrorCode.PARAMS_ERROR);
        }

        // 获取数据源
        DataSource dataSource = this.getById(dataSourceId);
        if (dataSource == null) {
            throw new BusinessException(ErrorCode.NOT_FOUND_ERROR, "数据源不存在");
        }

        // 检查数据源状态
        if (dataSource.getStatus() != 1) {
            throw new BusinessException(ErrorCode.FORBIDDEN_ERROR, "数据源已禁用");
        }

        // 执行SQL
        try (Connection conn = getConnection(dataSource)) {
            if (conn == null) {
                throw new BusinessException(ErrorCode.OPERATION_ERROR, "无法连接到数据源");
            }

            // 判断SQL类型
            sql = sql.trim();
            if (sql.toLowerCase().startsWith("select")) {
                // 查询SQL
                return executeQuery(conn, sql);
            } else {
                // 更新SQL
                return executeUpdate(conn, sql);
            }
        } catch (SQLException e) {
            log.error("SQL执行失败", e);
            throw new BusinessException(ErrorCode.OPERATION_ERROR, "SQL执行失败: " + e.getMessage());
        }
    }

    /**
     * 获取数据库连接
     *
     * @param dataSource 数据源信息
     * @return 数据库连接
     */
    private Connection getConnection(DataSource dataSource) {
        try {
            String type = dataSource.getType();
            // 加载驱动
            switch (type.toLowerCase()) {
                case "mysql":
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    break;
                case "oracle":
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    break;
                case "postgresql":
                    Class.forName("org.postgresql.Driver");
                    break;
                case "sqlserver":
                    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                    break;
                default:
                    throw new BusinessException(ErrorCode.PARAMS_ERROR, "不支持的数据库类型");
            }

            // 获取连接，如果密码已加密，则解密
            String password = dataSource.getPassword();
            if (password != null && !password.isEmpty()) {
                password = EncryptionUtils.decrypt(password);
            }
            
            // 处理MySQL连接URL，添加必要的参数
            String url = dataSource.getUrl();
            if ("mysql".equalsIgnoreCase(dataSource.getType()) && !url.contains("allowPublicKeyRetrieval=")) {
                // 添加allowPublicKeyRetrieval=true参数
                if (url.contains("?")) {
                    url += "&allowPublicKeyRetrieval=true";
                } else {
                    url += "?allowPublicKeyRetrieval=true";
                }
                
                // 如果没有useSSL参数，也添加useSSL=false
                if (!url.contains("useSSL=")) {
                    url += "&useSSL=false";
                }
            }
            
            return DriverManager.getConnection(
                    url,
                    dataSource.getUsername(),
                    password
            );
        } catch (ClassNotFoundException e) {
            log.error("数据库驱动加载失败", e);
            throw new BusinessException(ErrorCode.OPERATION_ERROR, "数据库驱动加载失败: " + e.getMessage());
        } catch (SQLException e) {
            log.error("数据库连接失败", e);
            throw new BusinessException(ErrorCode.OPERATION_ERROR, "数据库连接失败: " + e.getMessage());
        }
    }

    /**
     * 执行查询SQL
     *
     * @param conn 数据库连接
     * @param sql SQL语句
     * @return 查询结果
     */
    private List<Map<String, Object>> executeQuery(Connection conn, String sql) throws SQLException {
        List<Map<String, Object>> resultList = new ArrayList<>();

        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 处理结果集
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                resultList.add(row);
            }
        }

        return resultList;
    }

    /**
     * 执行更新SQL
     *
     * @param conn 数据库连接
     * @param sql SQL语句
     * @return 影响行数
     */
    private int executeUpdate(Connection conn, String sql) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            return stmt.executeUpdate(sql);
        }
    }

    /**
     * 检查是否支持的数据库类型
     *
     * @param type 数据库类型
     * @return 是否支持
     */
    private boolean isSupportedDatabaseType(String type) {
        if (StringUtils.isBlank(type)) {
            return false;
        }

        type = type.toLowerCase();
        return "mysql".equals(type) || "oracle".equals(type)
                || "postgresql".equals(type) || "sqlserver".equals(type);
    }

    /**
     * 校验JDBC URL格式
     *
     * @param url JDBC URL
     * @param type 数据库类型
     * @return 是否有效
     */
    private boolean isValidJdbcUrl(String url, String type) {
        if (StringUtils.isBlank(url) || StringUtils.isBlank(type)) {
            return false;
        }

        type = type.toLowerCase();
        url = url.toLowerCase();

        switch (type) {
            case "mysql":
                return url.startsWith("jdbc:mysql://");
            case "oracle":
                return url.startsWith("jdbc:oracle:thin:@");
            case "postgresql":
                return url.startsWith("jdbc:postgresql://");
            case "sqlserver":
                return url.startsWith("jdbc:sqlserver://");
            default:
                return false;
        }
    }
    
    @Override
    public boolean save(DataSource entity) {
        // 加密密码
        if (entity != null && entity.getPassword() != null && !entity.getPassword().isEmpty()) {
            entity.setPassword(EncryptionUtils.encrypt(entity.getPassword()));
        }
        return super.save(entity);
    }
    
    @Override
    public boolean updateById(DataSource entity) {
        // 加密密码
        if (entity != null && entity.getPassword() != null && !entity.getPassword().isEmpty()) {
            entity.setPassword(EncryptionUtils.encrypt(entity.getPassword()));
        }
        return super.updateById(entity);
    }
}
